package com.zxm.sunshineaiary.sql.db;

import java.io.File;
import java.io.IOException;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.os.Environment;
import android.util.Log;

public class DBadapter {

	private final String DB_SD = Environment.getExternalStorageDirectory()
			.getPath()
			+ "/SunshineDiary/diary.db";
	private final String DB_NAME = "diary.db";// 库名

	private final String TABLE_NAME = "diary";// 日记表名
	private final String DIARY_DATE = "date";// 日记日期
	private final String DIARY_IMAGE_NAME = "imageName";// 图片的递增名
	private final String DIARY_URL = "url";// 日记SD卡的地址
	private final String DIARY_WEATHER = "weather";// 日记颜色<备用>

	private final String TABLE_SETTING_NAME = "setting";// 配置表名
	private final String SETTING_YEAR = "year";// 配置年份

	private final int MKT_VERSION = 1;

	private SQLiteDatabase db;
	private DatabaseHelper dbhelper;

	public DBadapter(Context context) {
		dbhelper = new DatabaseHelper(context, DB_NAME, null, MKT_VERSION);

		File f = new File(DB_SD);
		if (!f.exists()) {
			// 文件存在返回false
			try {
				f.createNewFile();
				db = SQLiteDatabase.openOrCreateDatabase(DB_SD, null);
				dbhelper.onCreate(db);
				db.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * 增加diary表的 数据
	 * 
	 * @param date
	 *            日期
	 * @param imageName
	 *            图片名
	 * @param url
	 *            存储地址
	 * @param color
	 *            颜色
	 */
	public void insertDiary(String date, String imageName, String url,
			String color) {
		String sql = "insert into " + TABLE_NAME + "(" + DIARY_DATE + ","
				+ DIARY_IMAGE_NAME + "," + DIARY_URL + "," + DIARY_WEATHER
				+ ") values(?,?,?,?)";
		Object[] args = new Object[] { date, imageName, url, color };
		db.execSQL(sql, args);
	}

	/**
	 * 根据日期date删除Diary数据
	 * 
	 * @param date
	 */
	public void deleteDiary(String date) {
		String sql = "delete from  " + TABLE_NAME + " where " + DIARY_DATE
				+ "= ?";
		Object[] args = new Object[] { date };
		db.execSQL(sql, args);
	}

	/**
	 * 根据日期查找Diary数据
	 * 
	 * @param date
	 * @return
	 */
	public Cursor getDiary(String date) {
		String sql = "select " + DIARY_IMAGE_NAME + "," + DIARY_URL + ","
				+ DIARY_WEATHER + " from " + TABLE_NAME + " where "
				+ DIARY_DATE + " = ?";
		String[] args = new String[] { date };
		Cursor cursor = db.rawQuery(sql, args);
		return cursor;
	}

	/**
	 * 根据日期String查询出完整日期
	 * 
	 * @param date
	 * @return
	 */
	public Cursor getDiaryDate(String date) {
		String sql = "select " + DIARY_DATE + " from " + TABLE_NAME + " where "
				+ DIARY_DATE + " like " + date;
		Cursor cursor = db.rawQuery(sql, null);
		return cursor;
	}

	/**
	 * 增加diary表的ImageName列数据
	 * 
	 * @param imageName
	 */
	public void insertImageName(String imageName) {
		String sql = "insert into " + TABLE_NAME + "(" + DIARY_IMAGE_NAME
				+ ") values(?)";
		Object[] args = new Object[] { imageName };
		db.execSQL(sql, args);
	}

	/**
	 * 根据日期获得ImageName列数据
	 * 
	 * @param date
	 * @return
	 */
	public Cursor getImageName(String date) {
		String sql = "select " + DIARY_IMAGE_NAME + " from " + TABLE_NAME
				+ " where " + DIARY_DATE + " = ?";
		String[] args = new String[] { date };
		Cursor cursor = db.rawQuery(sql, args);
		return cursor;
	}

	/**
	 * 根据date日期来更改imageName
	 * 
	 * @param date
	 */
	public void upDateImageName(String imageName, String date) {
		String sql = "update " + TABLE_NAME + " set " + DIARY_IMAGE_NAME
				+ " =? where " + DIARY_DATE + " = '" + date + "'";

		Object[] args = new Object[] { imageName };
		db.execSQL(sql, args);
	}

	public void upDateWeather(String weather, String date) {
		String sql = "update " + TABLE_NAME + " set " + DIARY_WEATHER
				+ " =? where " + DIARY_DATE + " = '" + date + "'";

		Object[] args = new Object[] { weather };
		db.execSQL(sql, args);
	}

	/**
	 * 增加setting表的year列数据
	 * 
	 * @param year
	 */
	public void insertSettingYear(String year) {
		String sql = "insert into " + TABLE_SETTING_NAME + "(" + SETTING_YEAR
				+ ") values(?)";
		Object[] args = new Object[] { year };
		db.execSQL(sql, args);
	}

	/**
	 * 更改setting表的year列数据
	 * 
	 * @param year
	 */
	public void upDateSettingYear(String year, String id) {
		String sql = "update " + TABLE_SETTING_NAME + " set " + SETTING_YEAR
				+ " =? where _id=" + id;
		Object[] args = new Object[] { year };
		db.execSQL(sql, args);
	}

	/**
	 * 根据Id查询setting表的year列数据
	 * 
	 * @param id
	 * @return
	 */
	public Cursor getSettingYear(String id) {
		String sql = "select " + SETTING_YEAR + " from " + TABLE_SETTING_NAME
				+ " where appid=?";
		String[] args = new String[] { id };
		Cursor cursor = db.rawQuery(sql, args);
		return cursor;
	}

	/**
	 * 查询setting表的year列全部数据
	 * 
	 * @param id
	 * @return
	 */
	public Cursor getSettingYearAll() {
		String sql = "select " + SETTING_YEAR + " from " + TABLE_SETTING_NAME;
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(sql, null);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cursor;
	}

	/**
	 * 根据应用ID返回结果集
	 */
	public Cursor getAppId(String id) {
		String sql = "select appid from market where appid=" + id;
		Cursor appid = db.rawQuery(sql, null);
		return appid;

	}

	/**
	 * 根据_id查询appId
	 * 
	 * @param id
	 * @return
	 */
	public Cursor getAppId(long id) {
		String sql = "select appid,appname from market where _id=" + id;
		Cursor appid = db.rawQuery(sql, null);
		return appid;
	}

	public Cursor getAllByCursor() {
		String selectAll = "select * from '" + TABLE_NAME + "'";
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(selectAll, null);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cursor;
	}

	public void open() throws SQLException {
		db = SQLiteDatabase.openOrCreateDatabase(DB_SD, null);
	}

	/**
	 * 关闭数据库
	 */
	public void closeDb() {
		if (null != db) {
			db.close();
		}
	}

	class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, null, version);

		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i("xxx", "oncreate()xxx");
			String sql = "CREATE TABLE " + TABLE_NAME
					+ " (_id integer primary key autoincrement," + DIARY_DATE
					+ " VARCHAR(20) NOT NULL ON CONFLICT FAIL,"
					+ DIARY_IMAGE_NAME
					+ " VARCHAR(20) NOT NULL ON CONFLICT FAIL," + DIARY_URL
					+ " VARCHAR(40)," + DIARY_WEATHER + " VARCHAR(20))";

			String setting = "CREATE TABLE " + TABLE_SETTING_NAME
					+ " (_id integer primary key autoincrement," + SETTING_YEAR
					+ " VARCHAR(20) NOT NULL ON CONFLICT FAIL)";
			db.execSQL(sql);
			db.execSQL(setting);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
		}

	}

}
